﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Linq;
using System.Collections.Generic;

namespace EdalSoft.Data
{
    public class SQL
    {
        private SqlConnection con;
        private SqlTransaction tran;
        private bool istransactional;

        public SqlConnection GetConnection
        { get { return con; } }

        public SqlTransaction GetTransaction
        { get { return tran; } }

        /// <summary>
        /// Test Database Connection
        /// </summary>
        /// <param name="conString">Specify the connection string for the connection</param>
        /// <returns></returns>
        public static bool TestConnection(string conString)
        {
            var conn = new SqlConnection(conString);
            conn.Open();
            conn.Close();
            conn.Dispose();
            return true;
        }

        /// <summary>
        /// Creates a new DataLayer instance with the Specified Connection
        /// </summary>
        /// <param name="server">Specify the server to connect</param>
        /// <param name="trusted">Specify if will use Windows Authentication</param>
        /// <param name="database">Specify the database to connect</param>
        /// <param name="userid">Specify the username</param>
        /// <param name="password">Specify the password</param>
        /// <param name="IsTransactional">Specify if the connection will be transactional</param>
        public SQL(string server, bool trusted, string database, string userid, string password, bool IsTransactional)
        {
            con = new SqlConnection(String.Format("server={0};database={1};{2}", server, database,
                (trusted ? "trusted_Connection=true" : string.Format("uid={0};pwd={1}", userid, password))));
            con.Open();
            if (IsTransactional)
            { tran = con.BeginTransaction(); }
        }

        /// <summary>
        /// Creates a new DataLayer instance with the specified Connection String
        /// </summary>
        /// <param name="conString">Specify the connection string for the connection</param>
        /// <param name="IsTransactional">Specify if the connection will be transactional</param>
        public SQL(string conString, bool IsTransactional = false)
        {
            con = new SqlConnection(conString);
            con.Open();
            if (IsTransactional)
            { tran = con.BeginTransaction(); }
            istransactional = IsTransactional;
        }

        /// <summary>
        /// Execute an Stored Procedure
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        public void ExecuteProcedure(string storedProcedure, Parameters parameters=null)
        {
            using (var cmd = new SqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("@" + sqlpar.Name, sqlpar.Value);
                if (istransactional)
                {
                    cmd.Transaction = tran;
                }
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                if (!istransactional)
                    cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Execute an Stored Procedure Asyncronously
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IAsyncResult ExecuteProcedure_ASync(string storedProcedure, Parameters parameters = null)
        {
            using (var cmd = new SqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("@" + sqlpar.Name, sqlpar.Value);
                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                if (!istransactional)
                    cmd.Connection.Close();
                return cmd.BeginExecuteNonQuery();
            }
        }

        /// <summary>
        /// Execute an Stored Procedure and Get Output Parameters
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public Parameters GetOutputParameters(string storedProcedure, Parameters parameters=null)
        {
            using (var cmd = new SqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                    {
                        //cmd.Parameters.AddWithValue("@" + sqlpar.Name, sqlpar.Value);
                        var par = new SqlParameter
                                      {
                                          ParameterName = "@" + sqlpar.Name,
                                          Value = sqlpar.Value,
                                          Size = sqlpar.Size
                                      };
                        switch (sqlpar.Direction)
                        {
                            case Parameters.Parameter.ParDirection.In:
                                par.Direction = ParameterDirection.Input;
                                break;
                            case Parameters.Parameter.ParDirection.Out:
                            case Parameters.Parameter.ParDirection.InOut:
                                par.Direction = ParameterDirection.InputOutput;
                                break;
                            default:
                                par.Direction = ParameterDirection.Input;
                                break;
                        }
                        cmd.Parameters.Add(par);
                    }

                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                if (!istransactional)
                    cmd.Connection.Close();

                var outpars = new Parameters();
                foreach (SqlParameter par in cmd.Parameters.Cast<SqlParameter>().Where(par => par.Direction == ParameterDirection.InputOutput))
                    outpars.Add(par.ParameterName, par.Value, Parameters.Parameter.ParDirection.Out);
                return outpars;
            }
        }

        /// <summary>
        /// Execute an Stored Procedure and Get the Scalar
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public object GetScalar(string storedProcedure, Parameters parameters=null)
        {
            using (var cmd = new SqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("@" + sqlpar.Name, sqlpar.Value);
                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                object res = cmd.ExecuteScalar();
                if (!istransactional)
                    cmd.Connection.Close();
                return res;
            }
        }

        /// <summary>
        /// Execute an Stored Procedure and Get a Datatable
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string storedProcedure, Parameters parameters = null)
        {
            using (var cmd = new SqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("@" + sqlpar.Name, sqlpar.Value);
                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                var dt = new DataTable();
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
                if (!istransactional)
                    cmd.Connection.Close();
                return dt;
            }
        }

        /// <summary>
        /// Execute an Stored Procedure and Get a Datatable
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable GetDataTable_PK(string storedProcedure, Parameters parameters = null, DataColumn[] pks=null)
        {
            using (var cmd = new SqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("@" + sqlpar.Name, sqlpar.Value);
                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                var dt = new DataTable();
                dt.Columns.Add();
                dt.Columns.Add();
                dt.PrimaryKey = new DataColumn[] { dt.Columns[0], dt.Columns[1] };
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));
                if (!istransactional)
                    cmd.Connection.Close();
                return dt;
            }
        }

        /// <summary>
        /// Execute an specified Query
        /// </summary>
        /// <param name="Query"></param>
        /// <param name="parameters"></param>
        public void ExecuteQuery(string Query, Parameters parameters=null)
        {
            using (var cmd = new SqlCommand(Query, con) { CommandType = CommandType.Text })
            {
                if (parameters != null)
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                        cmd.Parameters.AddWithValue("@" + sqlpar.Name, sqlpar.Value);

                if (istransactional)
                    cmd.Transaction = tran;
                if (cmd.Connection.State != ConnectionState.Open)
                    cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                if (!istransactional)
                    cmd.Connection.Close();
                cmd.Connection.Close();
            }
        }

        /// <summary>
        /// Commit actual transaction
        /// </summary>
        public void CommitTransaction()
        {
            tran.Commit();
            con.Close();
        }

        /// <summary>
        /// Rollback actual transaction
        /// </summary>
        public void RollbackTransaction()
        {
            tran.Rollback();
            con.Close();
        }

        /// <summary>
        /// Execute DataReader
        /// </summary>
        /// <param name="storedProcedure"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string storedProcedure, Parameters parameters=null)
        {
            using (var cmd = new SqlCommand(storedProcedure, con) { CommandType = CommandType.StoredProcedure })
            {
                if (parameters != null)
                {
                    foreach (Parameters.Parameter sqlpar in parameters.Items.Values)
                    { cmd.Parameters.AddWithValue("@" + sqlpar.Name, sqlpar.Value); }
                }
                if (istransactional)
                { cmd.Transaction = tran; }
                if (cmd.Connection.State == ConnectionState.Closed)
                { cmd.Connection.Open(); }
                return cmd.ExecuteReader();
            }
        }

        /// <summary>
        /// Close Connection
        /// </summary>
        public void CloseConnection()
        {
            if (con.State != ConnectionState.Closed)
            { con.Close(); }
        }
    }
}